package com.customization.test;

import com.customization.commons.Console;

import com.customization.commons.CustomActionNew;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import okhttp3.*;
import org.junit.Test;
import org.xlsx4j.sml.Col;
import weaver.angel.action.fna.ActionLogUtil;
import weaver.conn.RecordSet;
import weaver.general.Util;
import weaver.interfaces.workflow.action.Action;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;


/**
 * @author liutaihong
 * @version 1.0.0
 * @ClassName TestRecordUtil.java
 * @Description 本地调用服务器的实例,
 * @createTime 2020-04-28 14:31:00
 */
public class UpdateHisFp extends BaseTest {


    @Test
    public void with() {
        String sql = "select COMPANYNAME,LICENSE,EXPIREDATE,CVERSION from  license ";
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql);
        if (rs.next()) {
            System.out.println("公司名称:" + rs.getString(1));
            System.out.println("LICENSE:" + rs.getString(2));
            System.out.println("授权到期日期:" + rs.getString(3));
            System.out.println("版本:" + rs.getString(4));
            Console.log(sql);
        }

    }

    @Test
    public void FixPzInfo()  {
        RecordSet rs = new RecordSet();
        RecordSet rst = new RecordSet();
        RecordSet rs2 = new RecordSet();
        String sql=" select requestid  from  WORKFLOW_REQUESTBASE where  workflowid =100982 and CURRENTNODEID " +
                " in('178230','178229','193726')";
        rs.execute(sql);
        while(rs.next()){
           String requestid= rs.getString("requestid");
           rst.execute("select *  from  uf_fp where zylc='"+requestid+"'");
           if(rst.next()){
               String zjrq=rst.getString("jzrq");
               String usql="update FORMTABLE_MAIN_627 set pzrq='"+zjrq+"',gzqj='"+zjrq+"',zjrq='"+zjrq+"'   where  requestid='"+requestid+"'";
               rs2.execute(usql);
               Console.log(usql);
           }
        }
    }


    /**
     * 删除供应商为实际占用的凭证
     */
    @Test
    public void FixDelPzInfo()  {
        RecordSet rs = new RecordSet();
        RecordSet rst = new RecordSet();

        String sql="select requestid,dt.pzbh,dt.kjnd,dt.gsdm,dt.pzxmh,rq,hxjkyfkje,dt.qz from " +
                " formtable_main_634 f,formtable_main_634_dt6 dt where dt.mainid=f.id    and qz=0";
        rs.execute(sql);
        while(rs.next()){

                String requestid= rs.getString("requestid");
                String pzh=rs.getString("pzbh");
                String nf=rs.getString("kjnd");
                String gsdm=rs.getString("gsdm");
                String pzxmh=rs.getString("pzxmh");
                String del="--delete from uf_fnaVoucher " +
                        "where pzh='"+pzh+"' and  nf='"+nf+"' and gsdm='"+gsdm+"'and  kjpzh='"+pzxmh+"'   and  qzlc='"+requestid+"'";
                rst.execute(del);
                Console.log(del);

        }

    }


    /**
     * 修正OA生成凭证号 占用
     */
    @Test
    public void FixPZHZY0317()  {
        RecordSet rs = new RecordSet();
        RecordSet rst = new RecordSet();
        RecordSet rs2 = new RecordSet();
        String sql="select bxpz,hjnd,gsdm2,hjpzxxmh,requestid from  formtable_main_634 where bxpz  is not null " +
                "union all " +
                "select bxpz,hjnd,gsdm2,hjpzxxmh,requestid from  formtable_main_639 where bxpz  is not null  " +
                "union all " +
                "select bxpz,hjnd,gsdm as gsdm2,hjpzxxmh,requestid from  formtable_main_627 where bxpz  is not null " +
                "union all " +
                "select bxpz,hjnd,gsdm2,hjpzxxmh,requestid from  formtable_main_620 where bxpz  is not null  ";
        rs.execute(sql);
        System.out.println(sql);
        int c=rs.getCounts();
        int i=0;
        /*
        while (rs.next()){
            System.out.println((i++)+"/"+c);;

            String requestid= rs.getString("requestid");
            String hjnd= rs.getString("hjnd");
            String gsdm2= rs.getString("gsdm2");
            String hjpzxxmh= rs.getString("hjpzxxmh");
            String bxpz= rs.getString("bxpz");
            CustomActionNew.insertFnaVoucher(bxpz,hjnd,gsdm2,requestid,hjpzxxmh);
        }

         */

    }


    /**
     * 修正OA生成凭证号 占用
     */
    @Test
    public void FixPZHZY0317Detail()  {
        RecordSet rs = new RecordSet();
        RecordSet rst = new RecordSet();
        RecordSet rs2 = new RecordSet();
        String sql="select t.*  from (" +
                " select requestid,dt.pzbh,dt.hjnd,dt.gsdm,dt.pzxmh,rq,qkje from  formtable_main_639 f,formtable_main_639_dt11 dt where dt.mainid=f.id  " +
                " union all" +
                " select requestid,dt.pzbh,dt.hjnd,dt.gsdm,dt.pzxmh,rq,hxjkyfkje from  formtable_main_620 f,formtable_main_620_dt6 dt where dt.mainid=f.id " +
                " union all" +
                " select requestid,dt.pzbh,dt.kjnd,dt.gsdm,dt.pzxmh,rq,hxjkyfkje from  formtable_main_634 f,formtable_main_634_dt6 dt where dt.mainid=f.id   " +
                ")t where requestid ='3454983'";
        rs.execute(sql);
        int c=rs.getCounts();
        int i=0;
        while (rs.next()){

            String requestid= rs.getString("requestid");
            String hjnd= rs.getString("hjnd");
            String gsdm2= rs.getString("gsdm");
            String hjpzxxmh= rs.getString("pzxmh");
            String bxpz= rs.getString("pzbh");
            Console.log((++i)+"/"+c);
            CustomActionNew.insertFnaVoucher(bxpz,hjnd,gsdm2,requestid,hjpzxxmh);
        }

    }


    @Test
    public void FixFpInfo() throws IOException {

            /**
             流程编号 流程ID-明细表ID-凭证号码字段-会计年度字段-公司代码字段
             //  FI302  105987-4-pzh-hjnd-gsdm 和 FI301 同一张表单

             FI301、FI302    121482-4-pzh-hjnd-gsdm-jzrq
             FI201          105981-7-bxpz-hjnd-gsdm2-jzqj
             FI203          99985-3-bxpz-hjnd-gsdm2-gzrq
             FI205          102982-3-bxpz-hjnd-gsdm2-gzrq

             FI204 100982-3-bxpz-hjnd-gsdm-zjrq

             */

            ArrayList<String> arrayList=new ArrayList<>();

            arrayList.add("105981-7-bxpz-hjnd-gsdm2-jzqj");
            arrayList.add("99985-3-bxpz-hjnd-gsdm2-gzrq");
            arrayList.add("102982-3-bxpz-hjnd-gsdm2-gzrq");
            arrayList.add("100982-3-bxpz-hjnd-gsdm-zjrq");
            arrayList.add("121482-4-pzh-hjnd-gsdm-jzrq");


            for (String  obj:arrayList) {
                String wfid=obj.split("-")[0];
                String dtno=obj.split("-")[1];
                String bxpz=obj.split("-")[2];
                String hjnd=obj.split("-")[3];
                String gsdm=obj.split("-")[4];
                String jzrq=obj.split("-")[5];

                String sql = " select  * from workflow_base where id='"+wfid+"' ";
                //select  workflowname,formid from workflow_base where id='102982'
                RecordSet rs = new RecordSet();
                RecordSet rsm = new RecordSet();
                RecordSet rs2 = new RecordSet();
                RecordSet rst = new RecordSet();
                rs.execute(sql);
                String workflowname="";

                int formid=-1;
                if(rs.first()){
                   workflowname= rs.getString("workflowname");
                   formid= rs.getInt("formid");
                }

                if(formid!=-1) {
                    Console.log("--------------"+workflowname+"----------------");

                    sql = "select requestid  from formtable_main_" + Math.abs(formid)+" fm,formtable_main_" + Math.abs(formid)+"_dt"+dtno +" dt where dt.mainid= fm.id   group by requestid ";
                    Console.log(sql);
                    rs.execute(sql);
                    int co=rs.getCounts();
                    Console.log("共计:"+co);
                  //======
                    int  i=0;
                    while(rs.next()){
                        i++;
                        String  requestid= rs.getString("requestid");


                        String  mainsql = "select *  from formtable_main_" + Math.abs(formid)+" where requestid='"+requestid+"'";
                        rsm.execute(mainsql);
                        rsm.first();
                        String voucherNo=rsm.getString(bxpz);//凭证
                        String accountingTime=rsm.getString(jzrq);//记账日期
                        String KJND=rsm.getString(hjnd);
                        String GSDM=rsm.getString(gsdm);
                        String mainid=rsm.getString("id");


                        String upAccountingTime="update uf_fp set jzrq='"+accountingTime+"' where zylc='"+requestid+"'";
                        rs2.execute(upAccountingTime);

                        mainsql = "select *  from uf_fp  where zylc='"+requestid+"' and fixstatus='0' ";
                        Console.log(mainsql);
                        rs2.execute(mainsql);
                        if(rs2.next()){
                            Console.log("已经处理过");
                            continue;
                        }
                        Console.log("当前:"+i+"/"+co);

                        if(voucherNo.equals("")){
                            Console.log("没有凭证号");
                            continue;
                        }



                        String deteailSql = "select *  from formtable_main_" + Math.abs(formid)+"_dt"+dtno+" where mainid='"+mainid+"'";
                        rsm.execute(deteailSql);
                        if (rsm.getCounts() > 0) {


                            JSONObject json = new JSONObject();
                            json.put("useStatus", "1");//占用状态
                            json.put("accountStatus", "1");//报销状态
                            json.put("accountingNo", requestid);//流程单号
                            json.put("sourceSystem","OA");
                            json.put("voucherNo", voucherNo);
                            json.put("voucherStatus", "1");
                            json.put("accountingTime", accountingTime);
                            JSONArray invoiceList = new JSONArray();

                            //遍历明细行；
                            while (rsm.next()) {
                                JSONObject child = new JSONObject();
                                String fpid = Util.null2String(rsm.getString("fphm"));
                                rst.execute("select * from uf_fp   where id='" + fpid + "'");
                                if (rst.next()) {
                                    String invoicecode = rst.getString("invoicecode");
                                    String invoiceNumber = rst.getString("invoiceNumber");
                                    String invoicetype = rst.getString("invoicetype");
                                    child.put("invoiceCode", invoicecode);//发票代码
                                    child.put("invoiceNumber", invoiceNumber);//发票号码
                                    child.put("invoiceType", invoicetype);//发票类型
                                    invoiceList.add(child);
                                    String upSql="update uf_fp set zylc='"+requestid+"' where id='"+rst.getString("fphm")+"'";
                                    rs2.execute(upSql);
                                }
                            }
                            json.put("invoiceList", invoiceList);

                            String content = json.toString();
                            Console.log("RequestStr:" + content);
                            OkHttpClient client = new OkHttpClient().newBuilder()
                                    .build();
                            MediaType mediaType = MediaType.parse("application/json");
                            RequestBody body = RequestBody.create(mediaType, content);
                            String url ="http://10.20.16.91:8230";
                            Request okhttp3request = new Request.Builder()
                                    .url(url + "/aqjm/api/accStatus/syncAccStatusAqjm")
                                    .method("POST", body)
                                    .addHeader("Content-Type", "application/json")
                                    .build();

                            //===========记录日志 初始化 Start=========
                            ActionLogUtil logs=new ActionLogUtil();
                            logs.startExec();//标记调用接口开始时间
                            logs.setAddr(okhttp3request.url().toString());//目标地址
                            logs.setReqInfo(content.toString());//请求报文
                            logs.setWfId(requestid);//请求ID
                            logs.setExeUser(1);//执行人
                            logs.setExectype("BZ");
                            //===========记录日志 初始化 End=========

                            Response okhttp3response = client.newCall(okhttp3request).execute();




                            String respStr = okhttp3response.body().string();
                            //===========记录日志 结尾 Start=========
                            logs.setRepInfo(respStr);//响应报文
                            logs.endExec();//执行并保存日志
                            //===========记录日志 结尾 END=========
                            Console.log("respStr:" + respStr);

                            if (okhttp3response.isSuccessful()) {
                                        JSONObject res = JSONObject.fromObject(respStr);
                                    if (res.containsKey("success") && "true".equals(res.getString("success"))) {
                                        //2021/2/2 13:41 lth  百望记账成功后 删除本次报销相同批次但未被使用的数据
                                        String upSql="update uf_fp set fixstatus='0' where zylc='"+requestid+"'";
                                        rs2.execute(upSql);
                                    }
                                }


                        }else {
                            Console.log("明细表没有发票数据,不调用发票更新接口");
                        }

                    }


                }
            }
    }


    /**
     * 修复手动生产应付后补录的数据 20210319
     * @throws IOException
     */
    @Test
    public void FixFpInfoSD() throws IOException {

        RecordSet r = new RecordSet();
        RecordSet rs = new RecordSet();
        r.execute(" select * from uf_fixpz ");


        while(r.next()){
            String requestid=r.getString("wfid");
            String pzh=r.getString("pzh");
            String hjnd=r.getString("hjnd");
            String gsdm	=r.getString("gsdm");

            String upAccountingTime="--update uf_fp set pzhm='"+pzh+"',kjnd='"+hjnd+"',gsdm='"+gsdm+"',zylc='"+requestid+"',fixstatus='-99'  where zylc='"+requestid+"'";
            Console.log(upAccountingTime);
            rs.execute(upAccountingTime);

        }}


        /**
         * 推送补录的数据到报账
         */

        @Test
        public void FixFpInfoSD2Bw() throws IOException {


                      RecordSet rs = new RecordSet();
                      RecordSet rs2 = new RecordSet();
                      RecordSet rsm = new RecordSet();
                      RecordSet rst = new RecordSet();


                    String sql = " select * from uf_fixpz ";
                    Console.log(sql);
                    rs.execute(sql);
                    int co=rs.getCounts();
                    Console.log("共计:"+co);
                    //======
                    int  i=0;
                    while (rs.next()){
                        i++;

                        String requestid=rs.getString("wfid");
                        String voucherNo=rs.getString("pzh");
                        String KJND=rs.getString("hjnd");
                        String GSDM	=rs.getString("gsdm");

                        String accountingTime="2020-03-19";//记账日期



                        String  mainsql =   " select *  from uf_fp  where zylc='"+requestid+"' ";
                        Console.log(mainsql);
                        boolean  isdeal=false;
                        rs2.execute(mainsql);
                        if(rs2.next()&&rs2.getInt("fixstatus")==0){
                            Console.log("已经处理过");
                            isdeal=true;
                        }
                        Console.log("当前:"+i+"/"+co);
                        if(voucherNo.equals("")){
                            Console.log("没有凭证号");
                            isdeal=true;
                        }

                    if(isdeal) {
                        continue;
                    }

                        rsm.execute(mainsql);
                        if (rsm.getCounts() > 0) {


                            JSONObject json = new JSONObject();
                            json.put("useStatus", "1");//占用状态
                            json.put("accountStatus", "1");//报销状态
                            json.put("accountingNo", requestid);//流程单号
                            json.put("sourceSystem","OA");
                            json.put("voucherNo", voucherNo);
                            json.put("voucherStatus", "1");
                            json.put("accountingTime", accountingTime);
                            JSONArray invoiceList = new JSONArray();

                            rst.execute(mainsql);
                            //遍历明细行；
                            while (rst.next()) {
                                     JSONObject child = new JSONObject();

                                    String invoicecode = rst.getString("invoicecode");
                                    String invoiceNumber = rst.getString("invoiceNumber");
                                    String invoicetype = rst.getString("invoicetype");
                                    child.put("invoiceCode", invoicecode);//发票代码
                                    child.put("invoiceNumber", invoiceNumber);//发票号码
                                    child.put("invoiceType", invoicetype);//发票类型
                                    invoiceList.add(child);

                            }
                            json.put("invoiceList", invoiceList);

                            String content = json.toString();
                            Console.log("RequestStr:" + content);
                            OkHttpClient client = new OkHttpClient().newBuilder()
                                    .build();
                            MediaType mediaType = MediaType.parse("application/json");
                            RequestBody body = RequestBody.create(mediaType, content);
                            String url ="http://10.20.16.91:8230";
                            Request okhttp3request = new Request.Builder()
                                    .url(url + "/aqjm/api/accStatus/syncAccStatusAqjm")
                                    .method("POST", body)
                                    .addHeader("Content-Type", "application/json")
                                    .build();

                            //===========记录日志 初始化 Start=========
                            ActionLogUtil logs=new ActionLogUtil();
                            logs.startExec();//标记调用接口开始时间
                            logs.setAddr(okhttp3request.url().toString());//目标地址
                            logs.setReqInfo(content.toString());//请求报文
                            logs.setWfId(requestid);//请求ID
                            logs.setExeUser(1);//执行人
                            logs.setExectype("BZ");
                            //===========记录日志 初始化 End=========

                            Response okhttp3response = client.newCall(okhttp3request).execute();




                            String respStr = okhttp3response.body().string();
                            //===========记录日志 结尾 Start=========
                            logs.setRepInfo(respStr);//响应报文
                            logs.endExec();//执行并保存日志
                            //===========记录日志 结尾 END=========
                            Console.log("respStr:" + respStr);

                            if (okhttp3response.isSuccessful()) {
                                JSONObject res = JSONObject.fromObject(respStr);
                                if (res.containsKey("success") && "true".equals(res.getString("success"))) {
                                    //2021/2/2 13:41 lth  百望记账成功后 删除本次报销相同批次但未被使用的数据
                                    String upSql="update uf_fp set fixstatus='0' where zylc='"+requestid+"'";
                                    rs2.execute(upSql);
                                }
                            }


                        }else {
                            Console.log("明细表没有发票数据,不调用发票更新接口");
                        }

                    }


                }


}
